﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

namespace CsharpCodeGenerator
{
    public class Helper
    {
        Helper helper = new Helper();

        #region Version2008
        public static string Version2008
        {
            get
            {
                return "SELECT dbid AS ID, NULL AS ParentID, name AS Text FROM [master].[dbo].[sysdatabases] ORDER BY [name]";
            }
        }
        #endregion

        #region Version2000
        public static string Version2000
        {
            get
            {
                return "SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]";
            }
        }
        #endregion

        #region TablesAndColumns
        public static string TablesAndColumns
        {
            get
            {
                return @"USE {0};
                    SELECT  Cols.TABLE_NAME,
                            Cols.COLUMN_NAME,
                            Cols.ORDINAL_POSITION,
                            Cols.DATA_TYPE,
		                    Cols.NUMERIC_PRECISION,
		                    Cols.NUMERIC_SCALE,		
                            Cols.IS_NULLABLE,
                            Cols.CHARACTER_MAXIMUM_LENGTH,       
                            COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
                            ( SELECT    COUNT(KCU.COLUMN_NAME)
                              FROM      INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
                                        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME
                                                                                              AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
                                                                                              AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                              WHERE     KCU.TABLE_NAME = Cols.TABLE_NAME
                                        AND KCU.COLUMN_NAME = Cols.COLUMN_NAME
                            ) AS IsIndex
                    FROM    [INFORMATION_SCHEMA].[COLUMNS] Cols
                    ORDER BY Cols.TABLE_NAME,
                            Cols.ORDINAL_POSITION

                    ";
            }
        }
        #endregion

        #region ForeignKeys
        public static string ForeignKeys
        {
            get
            {
                return @"USE {0};
                    SELECT
                        K_Table = FK.TABLE_NAME,
                        FK_Column = CU.COLUMN_NAME,
                        PK_Table = PK.TABLE_NAME,
                        PK_Column = PT.COLUMN_NAME,
                        Constraint_Name = C.CONSTRAINT_NAME
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
                        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
                        INNER JOIN (
                            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
                            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                        ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

                    ";
            }
        }
        #endregion

        #region DropPorcedure
        public string DropPorcedure2
        {
            get
            {
                return @"
                    --
                    -- Dropping stored procedure {0:G} : 
                    --

                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[{0:G}]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
                      DROP PROCEDURE [dbo].[{0:G}]
  
                    GO
                    ";
            }
        }
        #endregion

        #region GetVersion
        public static string GetVersion(string Constr)
        {
            try
            {
                string ver = string.Empty;
                string[] temp;
                using (SqlConnection con = new SqlConnection(Constr))
                {
                    con.Open();
                    temp = con.ServerVersion.Split('.');
                }
                ver = temp[0];
                return ver;
            }
            catch (Exception ex)
            {
                return ex.Message;
                throw;
            }
        }
        #endregion

        #region TableList
        public static string TableList
        {
            get
            {
                return "SELECT TABLE_NAME AS tname FROM [{0}].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME <> 'dtproperties' ORDER BY TABLE_NAME";
            }
        }
        #endregion

        #region GetTableLists
        public static IDataReader GetTableLists(string ConStr, string Query, string TableName)
        {
            string CommandText = string.Format(TableList, Query);

            return SqlHelper.ExecuteReader(ConStr, CommandType.Text, CommandText);
        }
        #endregion

        #region GetDataBases
        public static IDataReader GetDataBases(string ConStr, string Query)
        {
            return SqlHelper.ExecuteReader(ConStr, CommandType.Text, Query);
        }
        #endregion

        #region GetTablesAndColumns
        public static List<Sql> GetTablesAndColumns(string ConStr, string TablesAndColumns)
        {
            IDataReader dr = SqlHelper.ExecuteReader(ConStr, CommandType.Text, TablesAndColumns);

            List<Sql> SqlList = new List<Sql>();
            while (dr.Read())
            {
                Sql sql = new Sql
                {
                    TableName = dr["Table_Name"].ToString(),
                    ColumnName = dr["Column_Name"].ToString(),
                    OrdinalPosition = dr["ORDINAL_POSITION"].ToString(),
                    DataType = dr["DATA_TYPE"].ToString(),
                    NumericPercision = dr["NUMERIC_PRECISION"].ToString(),
                    NumericScale = dr["NUMERIC_SCALE"].ToString(),
                    IsNullable = dr["IS_NULLABLE"].ToString(),
                    CharLengh = dr["CHARACTER_MAXIMUM_LENGTH"].ToString(),
                    IsIdentity = dr["IsIdentity"].ToString(),
                    IsIndex = dr["IsIndex"].ToString()
                };
                SqlList.Add(sql);
            }
            dr.Close();
            dr.Dispose();
            return SqlList;
        }
        #endregion

        #region GetForeignKeys
        public static List<ForeignKey> GetForeignKeys(string ConStr, string f)
        {
            IDataReader dr = SqlHelper.ExecuteReader(ConStr, CommandType.Text, f);

            List<ForeignKey> SqlList = new List<ForeignKey>();
            while (dr.Read())
            {
                ForeignKey sql = new ForeignKey
                {
                    K_Table = dr["K_Table"].ToString(),
                    FK_Column = dr["FK_Column"].ToString(),
                    PK_Table = dr["PK_Table"].ToString(),
                    PK_Column = dr["PK_Column"].ToString(),
                    Constraint_Name = dr["Constraint_Name"].ToString()
                };

                SqlList.Add(sql);
            }
            
            dr.Close();
            dr.Dispose();
            return SqlList;
        }
        #endregion
    }
}
